Imports System.Collections
Imports System.Data.OracleClient

Public Class clsGnrtProCBody

    Dim mStrScriptCode As String = ""
    Dim mCmdData As OracleCommand
    Dim mDrData As OracleDataReader
    Dim mDrStringIndc As OracleDataReader
    Dim mStrTableName As String = ""
  Dim clsobjFileGen As clsGnrtFile

    Public Function mFnGenerateProCBody(ByVal aStrTableName As String _
                                      ) As Boolean
        Dim lStrTableName As String
        Dim lStrCode As String = ""
        Dim lStrColName As String = ""
        Dim lStrTempColName As String = ""
        Dim lStrColDataType As String = ""
        Dim lStrVrblSuffix As String = ""
        Dim lStrColNulInd As String
        Dim lStrColPrm As String
        Dim lIntCNT As Integer

        Dim lStrPrnParm As String = ""
        Dim lStrPrnLst As String = "%s"
        Dim lStrColLst As String = ""
        Dim lStrTemCol As String = ""
        Dim lStrWhereLst As String = ""
        Dim lIntColCount As Integer = 0

        Dim lStrDatePre As String
        Dim lStrDateExt As String
        Dim lStrNoKeyCol As String

        Dim lStrTemp As String = ""
        Dim lStrTemp2 As String
    Dim lStrTmpStrucName As String
    Dim lStrTmpTableName As String

        If mFnCheckTableValidity(aStrTableName) = False Then
            MessageBox.Show("Table doesnot exist", gCONST_MSG_HEADER)
            Exit Function
        End If

    mStrTableName = aStrTableName

    lStrTmpTableName = aStrTableName.Substring((aStrTableName.IndexOf("_") + 1)) + "_DB"

    lStrTableName = aStrTableName.Substring((aStrTableName.IndexOf("_") + 1))
    lStrTableName = lStrTableName.Replace("_", " ")
    lStrTableName = StrConv(lStrTableName, VbStrConv.ProperCase)
    lStrTableName = lStrTableName.Replace(" ", "")

    lStrTmpStrucName = lStrTableName + "DB"

    clsobjFileGen = New clsGnrtFile()

        '---------------------------------------------------------------------------------------------------------------
        'Gen Header
        '---------------------------------------------------------------------------------------------------------------

        lStrCode = lStrCode + "/*************************************************************************/" + vbCrLf
        lStrCode = lStrCode + "/*              H E A D E R  I N F O R M A T I O N  B O X                */" + vbCrLf
        lStrCode = lStrCode + "/*************************************************************************/" + vbCrLf
        lStrCode = lStrCode + "/*                                                                       */" + vbCrLf
        lStrCode = lStrCode + "/*                    TCS CONFIDENTIAL INFORMATION                       */" + vbCrLf
        lStrCode = lStrCode + "/*                                                                       */" + vbCrLf
        lStrCode = lStrCode + "/*              COPYRIGHT (c) 2007 TCS All Rights Reserved               */" + vbCrLf
        lStrCode = lStrCode + "/*                                                                       */" + vbCrLf
        lStrCode = lStrCode + "/*  This module contains confidential and proprietary information of     */" + vbCrLf
        lStrCode = lStrCode + "/*  TCS, and any reproduction, disclosure or use in whole or in part     */" + vbCrLf
        lStrCode = lStrCode + "/*  is expressly forbidden except as may be specifically authorized      */" + vbCrLf
        lStrCode = lStrCode + "/*  by prior written agreement or permission of TCS.                     */" + vbCrLf
        lStrCode = lStrCode + "/*                                                                       */" + vbCrLf
        lStrCode = lStrCode + "/*************************************************************************/" + vbCrLf
        lStrCode = lStrCode + "/*************************************************************************/" + vbCrLf
        lStrCode = lStrCode + "/*   Author    : Code Generator                                          */" + vbCrLf
        lStrCode = lStrCode + "/*   Date      : " + Now.Date + Space(56 - CStr(Now.Date).Length) + "*/" + vbCrLf
        lStrCode = lStrCode + "/*   Synopsis  : This File contains the basic dml services based on      */" + vbCrLf
        lStrCode = lStrCode + "/*               primary key for " + mStrTableName + Space(40 - mStrTableName.Length) + "*/" + vbCrLf
        lStrCode = lStrCode + "/*                                                                       */" + vbCrLf
        lStrCode = lStrCode + "/*   Modifications : This code is generated and should not be modified   */" + vbCrLf
        lStrCode = lStrCode + "/*                   manually                                            */" + vbCrLf
        lStrCode = lStrCode + "/*************************************************************************/" + vbCrLf + vbCrLf

    lStrCode = lStrCode + "#include <" + lStrTmpStrucName + ".h>" + vbCrLf

        lStrCode = lStrCode + "" + vbCrLf
        lStrCode = lStrCode + "EXEC SQL INCLUDE sqlca.h;" + vbCrLf
        lStrCode = lStrCode + "" + vbCrLf

    lStrCode = lStrCode + "  EXEC SQL BEGIN DECLARE SECTION  ;" + vbCrLf

    'Gen Nullable indicator variables
    mprGetTableCols()
    lIntCNT = 0
    While mDrData.Read
      lIntCNT = lIntCNT + 1

      lStrColNulInd = mDrData("TAB_COL_NULL_INDC")

      If Trim(lStrColNulInd) = "N" Then    'need check value 'N'
        lStrColName = mDrData("TAB_COL_NAME")
        mprGetColName(lStrColName)

        lStrTempColName = lStrColName.Replace("_", " ")
        lStrTempColName = StrConv(lStrTempColName, VbStrConv.ProperCase)
        lStrTempColName = lStrTempColName.Replace(" ", "")

        lStrCode = lStrCode + "    INDC_VAR ind" + lStrTempColName + ";" + vbCrLf
      End If

      If mDrData.IsDBNull(4) Then
        lStrColPrm = ""
      Else
        lStrColPrm = mDrData("TTL_INDX_TYPE")
      End If

      If Trim(lStrColPrm) = "P" Then
        'Get Primary Columns Count
        lIntColCount = lIntColCount + 1
      End If
    End While

    If lIntColCount = 0 Then  'If table has no key columns
      lStrNoKeyCol = ";"
    Else
      lStrNoKeyCol = ""
    End If

    '
    lStrCode = lStrCode + "  EXEC SQL END DECLARE SECTION    ; " + vbCrLf
    lStrCode = lStrCode + "" + vbCrLf

        'Get Table Columns
        mprGetTableCols()


        '1-------------------------------------------------------------------------------------------------
        '   Gen Select Part
        '1-------------------------------------------------------------------------------------------------

    mprGenPartHead("Slct", lStrTmpStrucName, lStrCode)

        '
    lStrCode = lStrCode + "RETURN_VALUE Slct" + lStrTmpStrucName + "(" + vbCrLf + " " + lStrTmpTableName + " * p" + lStrTmpStrucName + ")" + vbCrLf
        lStrCode = lStrCode + "{" + vbCrLf
        lStrCode = lStrCode + "" + vbCrLf
       
        lStrCode = lStrCode + "  #ifdef DBG" + vbCrLf
        lStrCode = lStrCode + "    ProcessEventLog(TRC_DBG,ERR_DB,0,""%s""," + vbCrLf
    lStrCode = lStrCode + "                  "" Entered Slct" + lStrTmpStrucName + """);" + vbCrLf
        lStrCode = lStrCode + "  #endif	" + vbCrLf
        lStrCode = lStrCode + "" + vbCrLf                                'above ok, need test

        '2-------------------------------------------------------------------------------------------------------------------
        'Gen Select Part
        mprGetTableCols()
        lIntCNT = 0
        While mDrData.Read
            lIntCNT = lIntCNT + 1

            lStrColName = mDrData("TAB_COL_NAME")

            lStrTempColName = lStrColName
            'If the column's type is DATE, then add convert function
            If lStrTempColName.IndexOf("DATE") > 0 Then
                lStrTempColName = "TO_CHAR(" + lStrTempColName + vbCrLf
                lStrTempColName = lStrTempColName + "                  ,'DDMMYYYY HH24:MI:SS')"
            End If

            If lIntCNT = 1 Then
                lStrCode = lStrCode + "  EXEC SQL SELECT " + lStrTempColName + vbCrLf
            Else
                lStrCode = lStrCode + "                 ," + lStrTempColName + vbCrLf
            End If

        End While

        'Gen Into part
        mprGetTableCols()
        lIntCNT = 0
        While mDrData.Read
            lIntCNT = lIntCNT + 1

            lStrColName = mDrData("TAB_COL_NAME")
            mprGetColName(lStrColName)

            lStrTempColName = lStrColName.Replace("_", " ")
            lStrTempColName = StrConv(lStrTempColName, VbStrConv.ProperCase)
            lStrTempColName = lStrTempColName.Replace(" ", "")

            lStrColNulInd = mDrData("TAB_COL_NULL_INDC")
            If IsDBNull(mDrData("TDCM_BACK_END_DATA_TYPE")) Then
                lStrTemp2 = ""
            Else
                lStrTemp2 = mDrData("TDCM_BACK_END_DATA_TYPE")
            End If

            mfnRtrnVarPrfix(lStrTemp2, lStrVrblSuffix)
            mfnRtrnPrnPrfix(lStrVrblSuffix, lStrPrnParm)

            If Trim(lStrColNulInd) = "N" Then   'need check value 'N'
        lStrTempColName = ":p" + lStrTmpStrucName + "->" + lStrVrblSuffix + lStrTempColName + vbCrLf + "                 :ind" + lStrTempColName
            Else
        lStrTempColName = ":p" + lStrTmpStrucName + "->" + lStrVrblSuffix + lStrTempColName
            End If

            If lIntCNT = 1 Then
                lStrCode = lStrCode + "            INTO " + lStrTempColName + vbCrLf
            Else
                lStrCode = lStrCode + "                ," + lStrTempColName + vbCrLf
            End If

        End While

        lStrCode = lStrCode + "	    FROM " + mStrTableName + lStrNoKeyCol + vbCrLf

        If lIntColCount > 0 Then   ' If table has primary key
            lStrCode = lStrCode + "	   WHERE"

            'Add key columns
            mprGetTableCols()
            lIntCNT = 0
            While mDrData.Read

                lStrColName = mDrData("TAB_COL_NAME")

                lStrTempColName = lStrColName
                mprGetColName(lStrTempColName)

                lStrTempColName = lStrTempColName.Replace("_", " ")
                lStrTempColName = StrConv(lStrTempColName, VbStrConv.ProperCase)
                lStrTempColName = lStrTempColName.Replace(" ", "")

                lStrTemCol = lStrTempColName

                If mDrData.IsDBNull(4) Then
                    lStrColPrm = ""
                Else
                    lStrColPrm = mDrData("TTL_INDX_TYPE")
                End If

                If IsDBNull(mDrData("TDCM_BACK_END_DATA_TYPE")) Then
                    lStrTemp2 = ""
                Else
                    lStrTemp2 = mDrData("TDCM_BACK_END_DATA_TYPE")
                End If

                mfnRtrnVarPrfix(lStrTemp2, lStrVrblSuffix)

                'mfnRtrnVarPrfix(mDrData("TDCM_BACK_END_DATA_TYPE"), lStrVrblSuffix)
                mfnRtrnPrnPrfix(lStrVrblSuffix, lStrPrnParm)


                If Trim(lStrColPrm) = "P" Then
          lStrTempColName = lStrColName + vbCrLf + "               = :p" + lStrTmpStrucName + "->" + lStrVrblSuffix + lStrTempColName
                    'here need to handle
                    lIntCNT = lIntCNT + 1

                    'prepare data for step 3
                    lStrPrnLst = lStrPrnLst + "," + lStrPrnParm

                    If lIntCNT = 1 Then
                        If lIntCNT = lIntColCount Then
                            lStrCode = lStrCode + " " + lStrTempColName + ";" + vbCrLf
                            lStrWhereLst = lStrWhereLst + lStrTempColName + ";" + vbCrLf
                        Else
                            lStrCode = lStrCode + " " + lStrTempColName + vbCrLf
                            lStrWhereLst = lStrWhereLst + lStrTempColName + vbCrLf
                        End If

            lStrColLst = lStrColLst + "         ," + "p" + lStrTmpStrucName + "->" + lStrVrblSuffix + lStrTemCol + vbCrLf
                    Else

                        If lIntCNT = lIntColCount Then
                            lStrCode = lStrCode + "	     AND " + lStrTempColName + ";" + vbCrLf

                            'Gen Where Part and this will be used by Select,Update,Delete Services
                            lStrWhereLst = lStrWhereLst + "	     AND " + lStrTempColName + ";" + vbCrLf

              lStrColLst = lStrColLst + "         ," + "p" + lStrTmpStrucName + "->" + lStrVrblSuffix + lStrTemCol + vbCrLf
                        Else
                            lStrCode = lStrCode + "	     AND " + lStrTempColName + vbCrLf
                            'Gen Where Part and this will be used by Select,Update,Delete Services
                            lStrWhereLst = lStrWhereLst + "	     AND " + lStrTempColName + vbCrLf
              lStrColLst = lStrColLst + "         ," + "p" + lStrTmpStrucName + "->" + lStrVrblSuffix + lStrTemCol + vbCrLf
                        End If
                    End If

                End If

            End While                                               'above ok , need test, 'where' has some problem ?
        Else
            'Setup value of Process Log param

        End If

    lStrColLst = lStrColLst + "         );"
        '3----------------------------------------------------------------
        '
    mprGenPartFoot("Slct", lStrTmpStrucName, lStrCode, lStrPrnLst, lStrColLst)

        '----------------------------------------------------------------------------------------------------------------
        'Select End
        '----------------------------------------------------------------------------------------------------------------






        '2-------------------------------------------------------------------------------------------------
        '   Gen Update Part
        '1-------------------------------------------------------------------------------------------------
    mprGenPartHead("Updt", lStrTmpStrucName, lStrCode)

        '
    lStrTemp = "RETURN_VALUE Updt" + lStrTmpStrucName + "(" + vbCrLf + " " + lStrTmpTableName + " * p" + lStrTmpStrucName + ")" + vbCrLf
    lStrCode = lStrCode + "RETURN_VALUE Updt" + lStrTmpStrucName + "(" + vbCrLf + " " + lStrTmpTableName + " * p" + lStrTmpStrucName + ")" + vbCrLf
        lStrCode = lStrCode + "{" + vbCrLf
        lStrCode = lStrCode + "" + vbCrLf
        lStrCode = lStrCode + "  #ifdef DBG" + vbCrLf
        lStrCode = lStrCode + "    ProcessEventLog(TRC_DBG,ERR_DB,0,""%s""," + vbCrLf
    lStrCode = lStrCode + "                 "" Entered Updt" + lStrTmpStrucName + """);" + vbCrLf
        lStrCode = lStrCode + "  #endif	" + vbCrLf
        lStrCode = lStrCode + "" + vbCrLf                                'above ok, need test

        '2-------------------------------------------------------------------------------------------------------------------

        lStrCode = lStrCode + "  EXEC SQL UPDATE " + mStrTableName + vbCrLf

        mprGetTableCols()
        lIntCNT = 0
        While mDrData.Read
            lIntCNT = lIntCNT + 1

            lStrColName = mDrData("TAB_COL_NAME")
            lStrTempColName = lStrColName
            mprGetColName(lStrTempColName)

            lStrTempColName = lStrTempColName.Replace("_", " ")
            lStrTempColName = StrConv(lStrTempColName, VbStrConv.ProperCase)
            lStrTempColName = lStrTempColName.Replace(" ", "")

            If IsDBNull(mDrData("TDCM_BACK_END_DATA_TYPE")) Then
                lStrTemp2 = ""
            Else
                lStrTemp2 = mDrData("TDCM_BACK_END_DATA_TYPE")
            End If

            mfnRtrnVarPrfix(lStrTemp2, lStrVrblSuffix)

            'mfnRtrnVarPrfix(mDrData("TDCM_BACK_END_DATA_TYPE"), lStrVrblSuffix)
            lStrColNulInd = mDrData("TAB_COL_NULL_INDC")

            'If the column's type is DATE, then add convert function
            If lStrColName.IndexOf("DATE") > 0 Then
                lStrDatePre = "to_date("
                lStrDateExt = "                  ,'ddmmyyyy hh24:mi:ss')"
            Else
                lStrDatePre = ""
                lStrDateExt = ""
            End If

      ' If Trim(lStrColNulInd) = "N" Then
      'lStrTempColName = lStrDatePre + ":p" + lStrTmpStrucName + "->" + lStrVrblSuffix + lStrTempColName + vbCrLf + lStrDateExt + "                   :ind" + lStrTempColName   'modified, to be tested
      ' Else
      If lStrDateExt <> "" Then
        lStrTempColName = lStrDatePre + ":p" + lStrTmpStrucName + "->" + lStrVrblSuffix + lStrTempColName + vbCrLf + lStrDateExt
      Else
        lStrTempColName = lStrDatePre + ":p" + lStrTmpStrucName + "->" + lStrVrblSuffix + lStrTempColName
      End If

      '   End If

      If lIntCNT = 1 Then
        lStrCode = lStrCode + "              SET " + lStrColName + vbCrLf
        lStrCode = lStrCode + "                  = " + lStrTempColName + vbCrLf
      Else
        lStrCode = lStrCode + "                 ," + lStrColName + vbCrLf
        lStrCode = lStrCode + "                  = " + lStrTempColName + vbCrLf
      End If

    End While

        If lIntColCount > 0 Then   'if table has primay key
            lStrCode = lStrCode + "	   WHERE " + lStrWhereLst
        Else
            lStrCode = lStrCode + "    " + lStrNoKeyCol + vbCrLf
        End If



        '3----------------------------------------------------------------
        '
    mprGenPartFoot("Updt", lStrTmpStrucName, lStrCode, lStrPrnLst, lStrColLst)

        '----------------------------------------------------------------------------------------------------------------
        'Update End
        '----------------------------------------------------------------------------------------------------------------






        '3-------------------------------------------------------------------------------------------------
        '   Gen Delete Part
        '1-------------------------------------------------------------------------------------------------
    mprGenPartHead("Dlte", lStrTmpStrucName, lStrCode)

        '
    lStrTemp = " struct " + lStrTmpTableName + "   * p" + lStrTmpStrucName + " )"
        If lStrTemp.Length > 70 Then
      lStrCode = lStrCode + "RETURN_VALUE Dlte" + lStrTmpStrucName + "(" + vbCrLf + " struct " + lStrTmpTableName + vbCrLf + "   * p" + lStrTmpStrucName + " )" + vbCrLf
        Else
      lStrCode = lStrCode + "RETURN_VALUE Dlte" + lStrTmpStrucName + "(" + vbCrLf + " struct " + lStrTmpTableName + " * p" + lStrTmpStrucName + " )" + vbCrLf
        End If

        lStrCode = lStrCode + "{" + vbCrLf
        lStrCode = lStrCode + "" + vbCrLf
        lStrCode = lStrCode + "  #ifdef DBG" + vbCrLf
        lStrCode = lStrCode + "    ProcessEventLog(TRC_DBG,ERR_DB,0,""%s""," + vbCrLf
    lStrCode = lStrCode + "                 "" Entered Dlte" + lStrTmpStrucName + """);" + vbCrLf
        lStrCode = lStrCode + "  #endif	" + vbCrLf
        lStrCode = lStrCode + "" + vbCrLf                                'above ok, need test

        '2-------------------------------------------------------------------------------------------------------------------

        lStrCode = lStrCode + "  EXEC SQL DELETE" + vbCrLf
        lStrCode = lStrCode + "             FROM " + mStrTableName + lStrNoKeyCol + vbCrLf

        If lIntColCount > 0 Then  'If table has primary key
            lStrCode = lStrCode + "	   WHERE " + lStrWhereLst
        End If


        '3----------------------------------------------------------------
        '
    mprGenPartFoot("Dlte", lStrTmpStrucName, lStrCode, lStrPrnLst, lStrColLst)

        '----------------------------------------------------------------------------------------------------------------
        'Delete End
        '----------------------------------------------------------------------------------------------------------------





        '4-------------------------------------------------------------------------------------------------
        '   Gen Insert Part
        '1-------------------------------------------------------------------------------------------------
    mprGenPartHead("Insrt", lStrTmpStrucName, lStrCode)

        '
    lStrTemp = " struct " + lStrTmpTableName + " * p" + lStrTmpStrucName + " )"
        If lStrTemp.Length > 70 Then
      lStrCode = lStrCode + "RETURN_VALUE Insrt" + lStrTmpStrucName + "(" + vbCrLf + " struct " + lStrTmpTableName + vbCrLf + "   * p" + lStrTmpStrucName + " )" + vbCrLf
        Else
      lStrCode = lStrCode + "RETURN_VALUE Insrt" + lStrTmpStrucName + "(" + vbCrLf + " struct " + lStrTmpTableName + " * p" + lStrTmpStrucName + " )" + vbCrLf
        End If

        lStrCode = lStrCode + "{" + vbCrLf
        lStrCode = lStrCode + "" + vbCrLf
        lStrCode = lStrCode + "  #ifdef DBG" + vbCrLf
        lStrCode = lStrCode + "    ProcessEventLog(TRC_DBG,ERR_DB,0,""%s""," + vbCrLf
    lStrCode = lStrCode + "                 "" Entered Insrt" + lStrTmpStrucName + """);" + vbCrLf
        lStrCode = lStrCode + "  #endif	" + vbCrLf
        lStrCode = lStrCode + "" + vbCrLf                                'above ok, need test

        '2-------------------------------------------------------------------------------------------------------------------

        lStrCode = lStrCode + "  EXEC SQL INSERT INTO " + mStrTableName + vbCrLf
        lStrCode = lStrCode + "    (" + vbCrLf

        mprGetTableCols()
        lIntCNT = 0
        While mDrData.Read
            lIntCNT = lIntCNT + 1

            lStrColName = mDrData("TAB_COL_NAME")

            If lIntCNT = 1 Then
                lStrCode = lStrCode + "      " + lStrColName + vbCrLf
            Else
                lStrCode = lStrCode + "     ," + lStrColName + vbCrLf
            End If

        End While                                               'above ok , need test, where has some problem
        lStrCode = lStrCode + "    )" + vbCrLf
        lStrCode = lStrCode + "    VALUES(" + vbCrLf

        mprGetTableCols()
        lIntCNT = 0
        While mDrData.Read
            lIntCNT = lIntCNT + 1

            lStrColName = mDrData("TAB_COL_NAME")
            lStrTempColName = lStrColName
            mprGetColName(lStrTempColName)

            lStrTempColName = lStrTempColName.Replace("_", " ")
            lStrTempColName = StrConv(lStrTempColName, VbStrConv.ProperCase)
            lStrTempColName = lStrTempColName.Replace(" ", "")

            If IsDBNull(mDrData("TDCM_BACK_END_DATA_TYPE")) Then
                lStrTemp2 = ""
            Else
                lStrTemp2 = mDrData("TDCM_BACK_END_DATA_TYPE")
            End If

            mfnRtrnVarPrfix(lStrTemp2, lStrVrblSuffix)
            'mfnRtrnVarPrfix(mDrData("TDCM_BACK_END_DATA_TYPE"), lStrVrblSuffix)
            mfnRtrnPrnPrfix(lStrVrblSuffix, lStrPrnParm)

            lStrColNulInd = mDrData("TAB_COL_NULL_INDC")

            'If the column's type is DATE, then add convert function
            If lStrColName.IndexOf("DATE") > 0 Then
                lStrDatePre = "to_date("
                lStrDateExt = vbCrLf + "     ,'ddmmyyyy hh24:mi:ss')"
            Else
                lStrDatePre = ""
                lStrDateExt = ""
            End If

      'If Trim(lStrColNulInd) = "N" Then   'need check value 'N'
      'lStrTempColName = lStrDatePre + ":p" + lStrTmpStrucName + "->" + lStrVrblSuffix + lStrTempColName + lStrDateExt + vbCrLf + " :ind" + lStrTempColName
      'Else
      lStrTempColName = lStrDatePre + ":p" + lStrTmpStrucName + "->" + lStrVrblSuffix + lStrTempColName + lStrDateExt
      ' End If

      If lIntCNT = 1 Then
        lStrCode = lStrCode + "      " + lStrTempColName + vbCrLf
      Else
        lStrCode = lStrCode + "     ," + lStrTempColName + vbCrLf
      End If

    End While

        lStrCode = lStrCode + "    );" + vbCrLf


        '3----------------------------------------------------------------
        '
    mprGenPartFoot("Insrt", lStrTmpStrucName, lStrCode, lStrPrnLst, lStrColLst)
        '----------------------------------------------------------------------------------------------------------------
        'Insert End
        '----------------------------------------------------------------------------------------------------------------


        'Save File
    clsobjFileGen.mprGnrtProCBodyFile(lStrCode, lStrTmpStrucName, "ProC Files")

    End Function



    Private Function mprGetColName(ByRef aStrColName As String)
        Dim lIntPos As Integer

        lIntPos = aStrColName.IndexOf("_")
        aStrColName = aStrColName.Substring(lIntPos + 1)

    End Function


    Private Function mFnCheckTableValidity(ByVal aStrTableName As String) As Boolean

        Dim lStrQuery As String = ""
        Dim lIntCount As Integer


        lStrQuery = "select count(*) as CNT from TRDX_TABLES_MSTR where TABM_TABLE_NAME = UPPER('"
        lStrQuery = lStrQuery + aStrTableName + "')"

        mCmdData = New OracleCommand(lStrQuery, con)
        lIntCount = mCmdData.ExecuteScalar

        If lIntCount = 0 Then
            Return False
        Else
            Return True
        End If

    End Function




    Private Sub mprGetTableCols()

        Dim lStrQuery As String = ""



        lStrQuery = "select distinct TAB_COL_NAME , TAB_COL_SEQ_NO, TAB_COL_NULL_INDC,"
        lStrQuery = lStrQuery + "                TDCM_BACK_END_DATA_TYPE , TTL_INDX_TYPE, TTL_COLM_SEQC_NMBR"
        lStrQuery = lStrQuery + " from TRDX_TABLE_COLUMNS left join TRDX_APLCTN_VRBLS on TAB_VRBL_SERIAL_NMBR = TAV_SERIAL_NMBR "
        lStrQuery = lStrQuery + "      left join TRDX_DATA_CATEGORY_MSTR on TAV_DATA_CTGRY = TDCM_DATA_CTGRY "
    lStrQuery = lStrQuery + "       left join TRDX_TABL_INDX on TAB_COL_ID = TTL_COLM_IDXX and TTL_TABL_IDXX =  TAB_TABLE_ID AND TTL_INDX_TYPE = 'P'"
        lStrQuery = lStrQuery + " where TAB_TABLE_ID = ( select distinct TABM_TABLE_ID "
        lStrQuery = lStrQuery + "                                  from TRDX_TABLES_MSTR "
        lStrQuery = lStrQuery + "                                 where TABM_TABLE_NAME = upper('" + mStrTableName + "') ) "
        lStrQuery = lStrQuery + " order by TAB_COL_SEQ_NO ASC"

        mCmdData = New OracleCommand(lStrQuery, con)
        mDrData = mCmdData.ExecuteReader

    End Sub

    Private Sub mprGenPartHead(ByVal aOpr As String, ByVal aStrTableName As String, ByRef aStrCode As String)

        aStrCode = aStrCode + "/**************************************************************************" + vbCrLf
        aStrCode = aStrCode + "***************************************************************************" + vbCrLf
    aStrCode = aStrCode + "**   Function Name : " + aOpr + aStrTableName + Space(48 - aStrTableName.Length) + "**" + vbCrLf
        aStrCode = aStrCode + "**                                                                       **" + vbCrLf
        aStrCode = aStrCode + "**   Input Params  : p" + aStrTableName + Space(51 - aStrTableName.Length) + "**" + vbCrLf
        aStrCode = aStrCode + "**                                                                       **" + vbCrLf
    aStrCode = aStrCode + "**   Output Params : p" + aStrTableName + Space(53 - aStrTableName.Length) + "**" + vbCrLf
        aStrCode = aStrCode + "**                                                                       **" + vbCrLf
        aStrCode = aStrCode + "**   Description   : " + aOpr + " service                                      **" + vbCrLf
        aStrCode = aStrCode + "**                                                                       **" + vbCrLf
        aStrCode = aStrCode + "**   Return Value  : returns 0 on success and sqlcode on failure         **" + vbCrLf
        aStrCode = aStrCode + "**                                                                       **" + vbCrLf
        aStrCode = aStrCode + "**   Mod History   :                                                     **" + vbCrLf
        aStrCode = aStrCode + "***************************************************************************" + vbCrLf
        aStrCode = aStrCode + "**************************************************************************/" + vbCrLf
        aStrCode = aStrCode + "" + vbCrLf

    End Sub

    Private Sub mprGenPartFoot(ByVal aOpr As String, ByVal aStrTableName As String, ByRef aStrCode As String, ByVal aStrPrnlst As String, ByVal aStrColLst As String)

        aStrCode = aStrCode + "" + vbCrLf
        aStrCode = aStrCode + "  if( sqlca.sqlcode NOT_EQUAL 0 )" + vbCrLf
        aStrCode = aStrCode + "    {" + vbCrLf
        aStrCode = aStrCode + "      ProcessEventLog(TRC_DBG,ERR_DB,Warn(sqlca.sqlcode),""" + aStrPrnlst + """," + vbCrLf
    aStrCode = aStrCode + "         "" Error " + aOpr + aStrTableName + """"  + vbCrLf
    aStrCode = aStrCode + "" + aStrColLst + vbCrLf
    ' aStrCode = aStrCode + "" + ");" + vbCrLf
        aStrCode = aStrCode + "      return (sqlca.sqlcode) ;" + vbCrLf
        aStrCode = aStrCode + "" + vbCrLf
        aStrCode = aStrCode + "    }" + vbCrLf
        aStrCode = aStrCode + "" + vbCrLf
        aStrCode = aStrCode + "  #ifdef DBG" + vbCrLf
    aStrCode = aStrCode + "    ProcessEventLog(TRC_DBG,ERR_DB,0,""%s""," + vbCrLf + "        "" Exit " + aOpr + aStrTableName + """);" + vbCrLf
        aStrCode = aStrCode + "  #endif" + vbCrLf
        aStrCode = aStrCode + "" + vbCrLf
        aStrCode = aStrCode + "  return 0;" + vbCrLf
        aStrCode = aStrCode + "" + vbCrLf
        aStrCode = aStrCode + "}" + vbCrLf                   'above ok, need test

    End Sub




    Private Function mfnRtrnVarPrfix(ByVal aDataType As String _
                                   , ByRef aReturnType As String)

        Dim lPassResult As String = ""

        Select Case LCase((aDataType))

            Case ("double")
                lPassResult = "d"
            Case ("char array")
                lPassResult = "ca"
            Case ("long")
                lPassResult = "l"
            Case ("int")
                lPassResult = "i"
            Case ("date")
                lPassResult = "dt"
            Case Else
                lPassResult = ""
        End Select

        aReturnType = lPassResult

    End Function

    Private Function mfnRtrnPrnPrfix(ByVal aPrefix As String _
                                   , ByRef aReturnType As String)

        Dim lPassResult As String = ""

        Select Case LCase((aPrefix))

            Case ("c")
                lPassResult = "%c"
            Case ("d")
                lPassResult = "%d"
            Case ("ca")
                lPassResult = "%s"
            Case ("i")
                lPassResult = "%d"
            Case ("l")
                lPassResult = "%l"
            Case ("dt")
                lPassResult = "%l"
            Case ("s")
                lPassResult = "%h"
            Case ("ll")
                lPassResult = "%l"
            Case ("f")
                lPassResult = "%f"
            Case ("ld")
                lPassResult = "%f"
            Case Else
                lPassResult = ""
        End Select

        aReturnType = lPassResult

    End Function

End Class
